﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBUtlity;
using Models;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class GetNumService
    {
        #region 新增
        /// <summary>
        /// 新增一条记录
        /// </summary>
        /// <param name="getNum"></param>
        /// <returns></returns>
        public int InsertNum(GetNum getNum)
        {
            string sql = "insert into GetNum(WorkParentId,Business,ZNum,SapMold,SapProject,";
            sql += "OrderNumber,CostCenter) values(@WorkParentId,@Business,@ZNum,@SapMold,";
            sql += "@SapProject,@OrderNumber,@CostCenter)";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@WorkParentId",getNum.WorkParentId),
                new SqlParameter("@Business",getNum.Business),
                new SqlParameter("@ZNum",getNum.ZNum),
                new SqlParameter("@SapMold",getNum.SapMold),
                new SqlParameter("@SapProject",getNum.SapProject),
                new SqlParameter("@OrderNumber",getNum.OrderNumber),
                new SqlParameter("@CostCenter",getNum.CostCenter)
            };
            return Convert.ToInt32(SQLHelper.Update(sql, param));
        }
        #endregion

        #region 查询
        /// <summary>
        /// 查询指定业务的最后一条记录
        /// </summary>
        /// <param name="business"></param>
        /// <returns></returns>
        public GetNum GetLastInfo(string business)
        {
            string sql = "select top 1 * from GetNum where Business=@Business order by Id desc";
            SqlParameter[] param = new SqlParameter[]
            {
                 new SqlParameter("@Business",business)
            };
            SqlDataReader objReader = SQLHelper.GetReader(sql, param);

            GetNum result = null;
            if (objReader.Read())
            {
                result = new GetNum();
                result.ZNum = objReader["ZNum"].ToString();
            }
            return result;
        }
        /// <summary>
        /// 根据WorkParentId查询GetNum表中是否有记录
        /// </summary>
        /// <param name="workParentId"></param>
        /// <returns></returns>
        public int GetNum(int workParentId)
        {
            string sql = "select count(*) from GetNum where WorkParentId=@WorkParentId";
            return Convert.ToInt32(SQLHelper.GetSingleResult(sql, new SqlParameter[] { new SqlParameter("@WorkParentId", workParentId) }));
        }
        /// <summary>
        /// 获取取号信息
        /// 需要参数：WorkParentId
        /// </summary>
        /// <param name="workParentId"></param>
        /// <returns></returns>
        public GetNum GetInfo(int workParentId)
        {
            string sql = "select * from GetNum where WorkparentId=@WorkparentId";
            SqlParameter[] param = new SqlParameter[]
            {
                 new SqlParameter("@workParentId",workParentId)
            };
            SqlDataReader objReader = SQLHelper.GetReader(sql, param);
            GetNum result = null;
            if (objReader.Read())
            {
                result = new GetNum();
                result.Id = Convert.ToInt32(objReader["Id"]);
                result.WorkParentId = Convert.ToInt32(objReader["WorkParentId"]);
                result.Business = objReader["Business"].ToString();
                result.ZNum = objReader["ZNum"] is DBNull ? "" : objReader["ZNum"].ToString();
                result.SapMold = objReader["SapMold"] is DBNull ? "" : objReader["SapMold"].ToString();
                result.SapProject = objReader["SapProject"] is DBNull ? "" : objReader["SapProject"].ToString();
                result.OrderNumber = objReader["OrderNumber"] is DBNull ? "" : objReader["OrderNumber"].ToString();
                result.CostCenter = objReader["CostCenter"] is DBNull ? "" : objReader["CostCenter"].ToString();
            }
            objReader.Close();
            return result;
        }
        /// <summary>
        /// 获取业务名称对应的代号
        /// </summary>
        /// <param name="business"></param>
        /// <returns></returns>
        public string GetCode(string business)
        {
            string sql = "select Code from GetNumMould where Business=@Business";
            return Convert.ToString(SQLHelper.GetSingleResult(sql, new SqlParameter[] { new SqlParameter("@Business", business) }));
        }
        /// <summary>
        /// 获取给号详情信息
        /// 需要参数：专案号 模具号
        /// </summary>
        /// <param name="zNum"></param>
        /// <param name="sapMold"></param>
        /// <returns></returns>
        public List<GetNum> GetInfo(string zNum,string sapMold)
        {
            string sql = "select * from GetNum where  1=1";
            List<SqlParameter> param = new List<SqlParameter>();
            if (zNum.Length > 0) { sql += " and ZNum like '%'+@ZNum+'%'"; param.Add(new SqlParameter("@ZNum",zNum));}
            if (sapMold.Length > 0) { sql += " and SapMold like '%'+@SapMold+'%'"; param.Add(new SqlParameter("@SapMold", sapMold)); }
            List<GetNum> result = new List<GetNum>();
            SqlDataReader objReader = SQLHelper.GetReader(sql, param.ToArray());
            while(objReader.Read())
            {
                result.Add(
                    new GetNum
                    {
                        Business = objReader["Business"].ToString(),
                        ZNum = objReader["ZNum"].ToString(),
                        SapMold = objReader["SapMold"].ToString(),
                        SapProject = objReader["SapProject"].ToString(),
                        OrderNumber = objReader["OrderNumber"].ToString(),
                        CostCenter = objReader["CostCenter"].ToString()
                    }
                );
            }
            objReader.Close();
            return result;

        }
        #endregion

        #region 更改 
        public int UpdateNum(GetNum getNum)
        {
            string sql = "update GetNum set SapMold=@SapMold,";
            sql += "SapProject=@SapProject,OrderNumber=@OrderNumber,CostCenter=@CostCenter";
            sql += " where WorkParentId=@WorkParentId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@SapMold",getNum.SapMold),
                new SqlParameter("@SapProject",getNum.SapProject),
                new SqlParameter("@OrderNumber",getNum.OrderNumber),
                new SqlParameter("@CostCenter",getNum.CostCenter),
                new SqlParameter("@WorkParentId",getNum.WorkParentId)
            };
            return Convert.ToInt32(SQLHelper.Update(sql, param));
        }
        #endregion
    }
}
